﻿using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Entity;
using IRepository;

namespace Repository
{
    public class InventoryDetailsRepository : IInventoryDetailsRepository
    {
        private readonly IDbConnection _dbConnection;

        public InventoryDetailsRepository(IDbConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }

        public async Task<InventoryDetailsEntity> GetByIdAsync(int id)
        {
            var sql = "SELECT * FROM wms_knzy_Inventory_details WHERE inventory_detail_id = @Id AND is_delete = 0";
            return await _dbConnection.QueryFirstOrDefaultAsync<InventoryDetailsEntity>(sql, new { Id = id });
        }

        public async Task<List<InventoryDetailsEntity>> GetByInventoryListIdAsync(int inventoryListId)
        {
            var sql = "SELECT * FROM wms_knzy_Inventory_details WHERE inventory_list_id = @InventoryListId AND is_delete = 0";
            return (await _dbConnection.QueryAsync<InventoryDetailsEntity>(sql, new { InventoryListId = inventoryListId })).AsList();
        }

        public async Task AddRangeAsync(List<InventoryDetailsEntity> entities)
        {
            var sql = @"INSERT INTO wms_knzy_Inventory_details (
                            inventory_list_id, product_code, book_inventory, book_amount,
                            inventory_location, stkt_num, pl_num, inventory_status,
                            reference_unit_price, profit_and_loss_amount, note,
                            create_by, create_time, update_by, update_time, is_delete
                        ) VALUES (
                            @inventory_list_id, @product_code, @book_inventory, @book_amount,
                            @inventory_location, @stkt_num, @pl_num, @inventory_status,
                            @reference_unit_price, @profit_and_loss_amount, @note,
                            @create_by, @create_time, @update_by, @update_time, @is_delete
                        )";
            await _dbConnection.ExecuteAsync(sql, entities);
        }

        public async Task UpdateAsync(InventoryDetailsEntity entity)
        {
            var sql = @"UPDATE wms_knzy_Inventory_details SET
                            product_code = @product_code,
                            book_inventory = @book_inventory,
                            book_amount = @book_amount,
                            inventory_location = @inventory_location,
                            stkt_num = @stkt_num,
                            pl_num = @pl_num,
                            inventory_status = @inventory_status,
                            reference_unit_price = @reference_unit_price,
                            profit_and_loss_amount = @profit_and_loss_amount,
                            note = @note,
                            update_by = @update_by,
                            update_time = @update_time,
                            is_delete = @is_delete
                        WHERE inventory_detail_id = @inventory_detail_id";
            await _dbConnection.ExecuteAsync(sql, entity);
        }

        public async Task DeleteAsync(int id)
        {
            var sql = "UPDATE wms_knzy_Inventory_details SET is_delete = 1, update_time = GETDATE() WHERE inventory_detail_id = @Id";
            await _dbConnection.ExecuteAsync(sql, new { Id = id });
        }
        // 新增：按盘点单ID批量逻辑删除明细
        public async Task DeleteByListIdAsync(int inventoryListId)
        {
            var sql = @"UPDATE wms_knzy_Inventory_details 
                       SET is_delete = 1, 
                           update_time = GETDATE() 
                       WHERE inventory_list_id = @InventoryListId";
            await _dbConnection.ExecuteAsync(sql, new { InventoryListId = inventoryListId });
        }
        public async Task<List<InventoryDetailWithProductName>> GetDetailsWithProductNameAsync(int inventoryListId)
        {
            // SQL：关联明细表和产品表，通过product_code匹配名称
            var sql = @"SELECT 
                    d.*,  -- 明细表所有字段
                    p.product_name  -- 产品表的名称
                FROM wms_knzy_Inventory_details d
                LEFT JOIN wms_basic_product_information p 
                    ON d.product_code = p.product_code  -- 关联条件
                WHERE d.inventory_list_id = @InventoryListId 
                  AND d.is_delete = 0";  // 过滤未删除的明细

            return (await _dbConnection.QueryAsync<InventoryDetailWithProductName>(
                sql,
                new { InventoryListId = inventoryListId }
            )).AsList();
        }
        public async Task<List<InventoryDetailsEntity>> GetByInventoryListIdAsync(int listId, IDbTransaction transaction = null)
        {
            var sql = @"SELECT * FROM wms_knzy_inventory_details 
                WHERE inventory_list_id = @ListId AND is_delete = 0";

            return (await _dbConnection.QueryAsync<InventoryDetailsEntity>(
                sql,
                new { ListId = listId },
                transaction)).AsList();
        }
    }
}